"""
DB module

Copyright jaman.com 2005 (c)
"""
import array
import MySQLdb 
import time
import threading
from Queue import Queue, Empty, Full 

# import error messages in meta subsystem
from errcode import *
from singleton import Singleton

# constants for database transaction status
TRAN_SUCCESS    = 0
TRAN_FAILURE    = 1

class PrimaryKeyConstraintViolation(Exception): pass


# decorator that calls f and converts certain MySQLdb exceptions to our
# exceptions
def mysqlexception(f):
    def wrap(*args, **kwargs):
        try:
            return f(*args, **kwargs)

        except MySQLdb.IntegrityError, e:
            iMySqlErrorCode = e.args[0]
            if iMySqlErrorCode == 1062:
                raise PrimaryKeyConstraintViolation(e)
            raise

    return wrap


class DBConnection(object) :
    """
    DBConnection class

    This is a connection Object for maintining a connection to the database
    """
# PUBLIC FUNCTION:
    # Constructor
    def __init__(self,uHostName, uUserName,uPassword,uDBName, oPool=None):  
        self._iTranStatus = TRAN_SUCCESS # 0 Transaction status
        self._iTranLevel = 0             # transaction level
        
        self._conn = None                # Databse connection object
        self._cursor = None              # internal curosr object

        # the connection pool that the dbconnection comes from, it can
        # be None too if the connection does not come from a pool
        self._pool = oPool
        
       # get Connection  
        self._connect(uHostName, uUserName,uPassword,uDBName)
       
 
    # Destructor
    # close the database connection if conn exists
    def __del__(self):
        if ( self._conn != None):
            self._conn.close()
    
    
    # function to be called from dbconnection pool whenever
    # it obtain a db connection. If reset returns false, it would need to
    # destroy this DBConnection object and create a new one.
    def reset (self):
        self._iTranStatus = TRAN_SUCCESS
        self._iTranLevel = 0

        return self._isValid ()

    def getPool (self):
        return self._pool
    
    # Function to validate whether a connection is valid or not
    def _isValid(self):
        if not self._conn:
            return False
        
        # request for sysdate
        # if request staisfied then connection exists
        # otherwise the connection does not exist
        
        sysdate = None
        try:
            self._cursor.execute("SELECT SYSDATE();")
            sysdate = self._cursor.fetchone()
        except:
            # If exception occurs some problem with connection so
            # return false so that the connection can be reestablished
            self.close()
            return False
        
        if not sysdate:
            self.close()
            return False

        return True    
   
            
    # This function gets the last insert id for the current connection
    def getLastInsertID(self):
        # TODO major hack here, need to agree on a version then use on
        # or the other most likly the one in the "else" which is the 
        # newer version of MySQLDB::Cursor (1.2)
        if hasattr(self._cursor,'insert_id'):
            return self._cursor.insert_id()
        else:
            return self._cursor.lastrowid

    # This function gets the row count of the last operation
    def getLastRowCount(self):
        return self._cursor.rowcount

    # This function is a wrapper around the Fetchone() api of cursor
    def fetchOne(self):               
        tTempRecord =  self._cursor.fetchone()
        return self._decodeSingleRecord(tTempRecord)
    
    # This function is a wrapper around the Fetchall() api of cursor
    def fetchAll(self):          
        tTempRecord = self._cursor.fetchall()
        return self._decodeRecords(tTempRecord)
    
    # This function is a wrapper around the Fetchmany() api of cursor
    # TODO: To imlement decode the results into utf-8 as done in fetchall
    def fetchMany(self,iSize= 0):
        assert 0  # need to do above TODO before using
        return self._cursor.fetchmany(iSize)
    
    # This function is wrapper above cursor's execute
    #@mysqlexception
    def execute(self,sSQL,tBinds=None):
        vEncBinds = tBinds
        #check to see if I can iterate over the values, list or tuple
        if type(tBinds) in [type([]), type(())]:    
            vEncBinds=[]
            for vItem in tBinds:
                if type(vItem) in [ unicode ]:
                    vEncBinds.append(vItem.encode('utf-8'))
                else:
                    vEncBinds.append(vItem)
        #one element tuple is just the primitive so check for a unicode string
        elif type(tBinds) in [unicode]:    
            vEncBinds = tBinds.encode('utf-8')
            
        if vEncBinds != None:
            #ServerLog.getTheInstance().logDebug(UTIL_DB_SQL_STRING,
            #    "%s\n%s" % (sSQL, str(vEncBinds)))
            #print 'ServerLog if'
            pass
        else:
            #ServerLog.getTheInstance().logDebug(UTIL_DB_SQL_STRING, sSQL)
            #print 'ServerLog else'
            pass
        
        return self._cursor.execute(sSQL,vEncBinds)

    # This function is wrapper above cursor's executemany
    #@mysqlexception
    def executeMany(self, sSQL, atBinds=None):
        aaEncBinds = []
        for tBinds in atBinds:
            aEncBinds = []
            for vItem in tBinds:
                if type(vItem) in [ unicode ]:
                    aEncBinds.append(vItem.encode('utf-8'))
                else:
                    aEncBinds.append(vItem)
            aaEncBinds.append(aEncBinds)
    
        #ServerLog.getTheInstance().logDebug(UTIL_DB_SQL_STRING,
        #    "%s\n%s" % (sSQL, "\n".join([str(a) for a in aaEncBinds])))
        
        return self._cursor.executemany(sSQL, aaEncBinds)

    # This function begins transaction    
    def begin(self):
        self._cursor.execute("begin;")
    
    # This function rollbacks the transaction
    def rollback(self):
        self._conn.rollback()
    
    # This function commits the transaction    
    def commit(self):
        self._conn.commit()
    
    # Close the Database connection and sets the
    # conenction object to None
    def close(self):
        self._conn.close()
        self._conn = None

    # Function checks the whether a transaction succeeded or not
    def isFailure(self):        
        return self._iTranStatus != TRAN_SUCCESS 

#PROPERTIES
    # Property methods according to new style class    
    # property to set/get Tranlevel (Transaction level)
    def getTranLevel(self):
        return self._iTranLevel
    
    def setTranLevel(self,value):
        self._iTranLevel = value
    
    TranLevel = property(getTranLevel,setTranLevel,None,None) 
     
  
    # property  to set/get Transaction status
    def getTranStatus(self):
        return self._iTranStatus
    
    def setTranStatus(self,value):
        self._iTranStatus = value
    
    TranStatus = property(getTranStatus,setTranStatus,None,None)
    
    # property  to set/get db pool
    def getdbPool(self):
        return self._pool
    
    def setdbPool(self,value):
        self._pool = value
    
    dbPool = property(getdbPool,setdbPool,None,None)
    
# PRIVATE FUNCTIONS:
    
    def _getColumnIndexToDecode(self):
        aColumnIndexList = []
        
        for  column , desc in enumerate(self._cursor.description):
            # MySQLdb.BINARY is used for text columns 
            if(desc[1] in [MySQLdb.STRING, MySQLdb.BINARY]):
                aColumnIndexList.append(1)        
            else:            
                aColumnIndexList.append(0)
        
        return aColumnIndexList

    def _isDecodeNeeded(self,aIndexList):        
        for i in range(len(aIndexList)):
            if ( aIndexList[i] == 1):
                return True
        return False
                
        
    def _decodeSingleRecord(self,tTempRecord):
        
        # get the decode array list
        aDecodeList = self._getColumnIndexToDecode()
        if (not self._isDecodeNeeded(aDecodeList) or tTempRecord == None) :
            return tTempRecord

        aResult=[]
        iField = 0        
        aTempList = []
        # loop through the tuple and convert string type to
        # to unicode (utf-8)
        # create the converted tuple and return
        for iField in range(len(tTempRecord)):    
            if isinstance(tTempRecord[iField], array.array):  # BLOB
                aTempList.append(tTempRecord[iField].tostring())
            elif (aDecodeList[iField] == 1 and tTempRecord[iField] != None):
                aTempList.append((tTempRecord[iField]).decode('utf-8'))
            else:
                aTempList.append(tTempRecord[iField])
                        
        tResult = tuple(aTempList)
        return tResult

    def _decodeRecords(self, tTempRecord):
        # get the decode array list
        aDecodeList = self._getColumnIndexToDecode()
        if (not self._isDecodeNeeded(aDecodeList) or tTempRecord == None) :
            return tTempRecord
        
        aResult=[]
        iRow = 0
        iField = 0
        # loop through the tuple and convert string type to
        # to unicode (utf-8)
        # create the converted tuple and return
        for iRow in range(len(tTempRecord)):
            aTempList = []    
            for iField in range(len(tTempRecord[iRow])):    
                if isinstance(tTempRecord[iRow][iField], array.array):  # BLOB
                    aTempList.append(tTempRecord[iRow][iField].tostring())
                elif (aDecodeList[iField] == 1 and \
                    tTempRecord[iRow][iField] != None):
                    aTempList.append((tTempRecord[iRow][iField]).decode('utf-8'))
                else:
                    aTempList.append(tTempRecord[iRow][iField])

            # append the tuple to the aResult list       
            aResult.append(tuple(aTempList))
            
        # convert the resulting list of tuple to tuple of tuple
        tResult = tuple(aResult)
        
        # return the tuple of tuple
        return tResult
    
    
     # This method is used to connect to the  database and
     # return the connection object
    def _connect(self, hostname,username,password,dbname):
            try:
                self._conn = MySQLdb.connect(host=hostname,user=username,passwd=password,db=dbname,
                                             init_command='SET NAMES UTF8')

                #ServerLog.getTheInstance().logInfo(UTIL_DB_CONNECTED_TO_DB,
                #    hostname, username, dbname)

                self._cursor = self._conn.cursor()
            except Exception, e:
                if not self._conn:
                    # raise MonsoonSysException(UTIL_DB_INVALID_CONNECTION,
                    #    str(e))
                    #print 'raise MonsoonSysException(UTIL_DB_INVALID_CONNECTION,'
                    pass
                if not self._cursor:
                    #raise MonsoonSysException(UTIL_DB_INVALID_CONNECTION,
                    #    str(e))
                    #print 'raise MonsoonSysException(UTIL_DB_INVALID_CONNECTION,'
                    pass
                raise
                

# import all the config options from current application options
#from appoptions import *


class DBConnectionPool(Singleton):
    """
    DBConnectionPool class

    This is a Singleton Object for maintaining a connection pool upto maximum of 100 number
    This class would be instantiated only by the Meta data service and all the connection to
    the database are given by this class only

    USAGE: All the service accessing connection to the Databse will be using single
    instance of this class

    TODO:   
        
    """
    # constructor
    def __init__(self, uHostName, uUserName, uPassword, uDBName, iMaxConnection,
                 iMaxAttempts, iConnWaitTime):
       
        #oDynCfg = DynamicConfiguration.getTheInstance ()
        
        self._lLock = threading.Lock ()   
        
        # Set the maximum connection to a configurable parameter
        self._iMaxConnection = iMaxConnection 
        self._iMaxAttempts = iMaxAttempts
        self._iConnWaitTime = iConnWaitTime
        
        # keep tracks of number of active connection       
        self._iCreatedConnection = 0
        
        #initalize the Db pool with a queue of size = maximum connection
        self._queue = Queue(self._iMaxConnection)
        
        # intialize the connection string parameters
        self._uHostName = uHostName
        self._uUserName = uUserName
        self._uPassWord = uPassword
        self._uDBName =  uDBName
        
        # throw exception of any of hostname,username,password or
       # dbname is none
        if (self._uHostName == None or self._uHostName == "" \
            or self._uUserName == None or self._uUserName == "" \
            or self._uPassWord == None or self._uPassWord == "" \
            or self._uDBName == None or self._uDBName == ""):
            #raise MonsoonSysException(UTIL_DB_INVALID_CONNECT_PARAMETER)
            #print 'MonsoonSysException(UTIL_DB_INVALID_CONNECT_PARAMETER)'
            pass

    # This function is called whenever a new DB connection is needed
    # The function tries to get the connection from a queue
    # - if the queue is empty AND the total number of created connection
    #   is less than max then the function creates a new connection object
    #   and returns it
    # - if the queue is empty AND the total number of created connections
    #   is equal to max then it waits for sometime and again tries to get the
    #   connection from the pool. If it fails again then it tries till
    #   iMaxAttempts and the raises a MonsoonSysException    
    
    def getConnection(self):
        iAttempts = 0
        for iAttempts in range (self._iMaxAttempts):

            try:
                obj = self._queue.get_nowait ()
                obj.dbPool = self
            except Empty:

                bCreateConnect = False
                try:
                    self._lLock.acquire ()
                    if self._iCreatedConnection < self._iMaxConnection:
                       self._iCreatedConnection += 1
                       bCreateConnect = True

                finally:
                    self._lLock.release ()
                
                if bCreateConnect:
                    try:
                        
                        obj = DBConnection (self._uHostName,self._uUserName, \
                                            self._uPassWord, self._uDBName, \
                                            self)                    
                    except:
                        try:
                            self._lLock.acquire ()
                            self._iCreatedConnection -= 1
                        finally:
                            self._lLock.release ()
                        raise

                else:
                    try:
                        obj = self._queue.get(True,self._iConnWaitTime)
                        obj.dbPool = self
                    except Empty:
                        continue

            if (obj == None):
                continue

            # check if connection is valid by issuing a SYSDATE
            # function.
            if (obj.reset() == False):                
                del obj
                try:
                    obj = DBConnection (self._uHostName,self._uUserName, \
                                        self._uPassWord, self._uDBName, self)
                except:
                    try:
                        self._lLock.acquire ()
                        self._iCreatedConnection -= 1
                    finally:
                        self._lLock.release ()
                    raise            
            #ServerLog.getTheInstance().logDebug(UTIL_DB_GETCONNECTION_SUCCESS)
            return obj
            
                
        # if we reached here that means
        # we have max connection in the pool and
        # we are not able to get any connection from the pool
        #raise MonsoonSysException(UTIL_DB_CONNECTION_NOT_AVAILABLE)
        #print 'MonsoonSysException(UTIL_DB_CONNECTION_NOT_AVAILABLE)'

    # This method put backs the connnection object back to the pool
    # along with time of commiting to the pool
    def putConnection(self, obj):
        
        try:               
            obj.Tranlevel = 0  #To make the Tranlevel to zero
            obj.TranStatus = TRAN_SUCCESS
            obj.dbPool = None
            self._queue.put(obj)
            ServerLog.getTheInstance().logDebug(UTIL_DB_PUTCONNECTION_SUCCESS)
        except Full:
            # raise an exception as connection pool exception
            # raise MonsoonSysException(UTIL_DB_CONNECTION_POOL_EXCEPTION)
            #print 'MonsoonSysException(UTIL_DB_CONNECTION_POOL_EXCEPTION)'
            pass
            
    
        
        




class StartDBTransaction (object):
    """
    StartDBTransaction class to intiate a transaction and complete/rollback
    the transaction accordingly.

    Copyright jaman.com 2005 (c)
    """

    # Constructor to take in connection object and
    # set transaction level and if the transaction level
    # is one then start a transaction
    def __init__(self,odbConnection):
        
        self.bInitialized = False
        # throw exception if dbconnection already has failure status
        if (odbConnection.TranStatus != TRAN_SUCCESS):
            #raise MonsoonSysException (UTIL_DB_TRANSACTION_FAILURE)
            #print "MonsoonSysException (UTIL_DB_TRANSACTION_FAILURE)"
            pass
        
        # always assume failure in StartDBTransaction to safeguard error
        # path.
        self.bTranStatus = TRAN_FAILURE 
        self._connection = odbConnection        
        self._connection.TranLevel += 1
        if  self._connection.TranLevel == 1 :
            self._connection.begin()

        self.bInitialized = True

    # destructor
    # If transaction fails then it rollbacks the transaction
    # otherwise if the refrence count iTranlevel becomes zero
    # it commits the transaction into database
    def __del__(self):

        # do not go thru the destructor logic if the constructor is not 
        # successful since constructor can throw exception
        if not self.bInitialized:
            return

        # Update dbconnection transaction status to failure if necessary
        if self.bTranStatus != TRAN_SUCCESS:
            self._connection.TranStatus = self.bTranStatus
            #ServerLog.getTheInstance().logInfo(UTIL_DB_TRANSACTION_FAILURE)
        self._connection.TranLevel -= 1
        
        # commit/rollback when tranlevel = 0
        if self._connection.TranLevel == 0:
            if self._connection.TranStatus != TRAN_SUCCESS: #if not success
                self._connection.rollback ()
                #ServerLog.getTheInstance().logInfo(
                #    UTIL_DB_TRANSACTION_ROLLED_BACK)
            else:
                self._connection.commit ()
                #ServerLog.getTheInstance().logDebug(
                #    UTIL_DB_TRANSACTION_COMMITTED)

            # if the connection comes from a pool originally, 
            # then we need to put the connection back to the pool 
            oPool = self._connection.getPool ()
            if (oPool != None):
                oPool.putConnection (self._connection)
                oPool = None
                

        # set the connection to none otherwise it will close the connection
        self._connection = None

   
    # This function sets the iTransStatus to SUCCESS if the
    # transaction succeds. This function must be called before 
    # StartDBTransaction goes out of scope for us to indicate success 
    # execution of DB transaction.
    def setSuccess(self):
        # change to success status  
        self.bTranStatus = TRAN_SUCCESS 
